// scheduleStats.js
const { allAsync, getAsync, log } = require('./dbConfig');

const getPersonalScheduleCompletion = async (req, res) => {
  // 获取当前年份作为默认值
  const currentYear = new Date().getFullYear();
  const { year = currentYear } = req.query; // 默认使用当前年份，可通过参数传入

  try {
    // 步骤1: 获取所有内部成员的基本信息和年度单量指标
    const usersQuery = `
      SELECT id, user_name, year_indicator
      FROM sk_user_info
      WHERE is_internal = '0' AND del_flag = '0'
    `;
    const users = await allAsync(usersQuery);

    if (!users || users.length === 0) {
      return res.json({
        data: [],
        code: '0',
        success: true,
        msg: '无内部成员数据'
      });
    }

    // 步骤2: 计算每个内部成员的实际完成数
    const userIds = users.map(user => user.id).join(',');
    const completionQuery = `
      SELECT u.id AS fixed_gear_id, COALESCE(COUNT(s.id), 0) as completed_count
      FROM sk_user_info u
      LEFT JOIN sk_schedule s ON u.id = s.fixed_gear_id 
        AND s.del_flag = '0' 
        AND strftime('%Y', s.schedule_date) = ?
      WHERE u.is_internal = '0' AND u.del_flag = '0'
      GROUP BY u.id
    `;
    const completionResults = await allAsync(completionQuery, [year.toString()]);

    // 步骤3: 合并数据并计算完成率
    const result = users.map(user => {
      const completion = completionResults.find(c => c.fixed_gear_id === user.id) || { completed_count: 0 };
      const totalCount = user.year_indicator || 0;
      const completedCount = completion.completed_count || 0;
      const completionRate = (totalCount > 0 && completedCount > 0) ? parseFloat(((completedCount / totalCount) * 100).toFixed(2)) : 0;

      return {
        user_name: user.user_name,
        total_count: totalCount,
        completed_count: completedCount,
        completion_rate: completionRate
      };
    });

    // 步骤4: 按完成率倒序排序
    result.sort((a, b) => {
      const rateA = parseFloat(a.completion_rate);
      const rateB = parseFloat(b.completion_rate);
      return rateB - rateA; // 倒序排序
    });

    // 返回结果
    res.json({
      data: result,
      code: '0',
      success: true,
      msg: '统计成功'
    });
  } catch (err) {
    log(`Get personal schedule completion error: ${err.message}`);
    res.status(500).json({ data: [],
        code: '1',
        success: false,
        msg: '获取个人档期完成情况失败，请稍后再试！' });
  }
};

const getDashboardStats = async (req, res) => {
  // 获取当前年份作为默认值
  const currentYear = new Date().getFullYear();
  const { year = currentYear } = req.query; // 默认使用当前年份，可通过参数传入

  try {
    // 步骤1: 基础信息数据汇总
    const basicDataQuery = `
      SELECT
        (SELECT COUNT(*) FROM sk_user_info WHERE occupation = '0' AND del_flag = '0') as hostCount,
        (SELECT COUNT(*) FROM sk_user_info WHERE occupation = '1' AND del_flag = '0') as supervisorCount,
        (SELECT COUNT(*) FROM sk_partner_company WHERE del_flag = '0') as partnerCompany
    `;
    const basicData = await getAsync(basicDataQuery);

    // 步骤2: 档期数据汇总
    const scheduleQuery = `
      SELECT
        SUM(CASE WHEN u.occupation = '0' THEN 1 ELSE 0 END) as host_count,
        SUM(CASE WHEN u.occupation = '0' AND u.is_internal = '0' THEN 1 ELSE 0 END) as host_domestic,
        SUM(CASE WHEN u.occupation = '0' AND u.is_internal = '1' THEN 1 ELSE 0 END) as host_dispatch,
        SUM(CASE WHEN u.occupation = '0' THEN s.profit ELSE 0 END) as host_income,
        SUM(CASE WHEN u.occupation = '1' THEN 1 ELSE 0 END) as supervisor_count,
        SUM(CASE WHEN u.occupation = '1' AND u.is_internal = '0' THEN 1 ELSE 0 END) as supervisor_domestic,
        SUM(CASE WHEN u.occupation = '1' AND u.is_internal = '1' THEN 1 ELSE 0 END) as supervisor_dispatch,
        SUM(CASE WHEN u.occupation = '1' THEN s.profit ELSE 0 END) as supervisor_income
      FROM sk_schedule s
      LEFT JOIN sk_user_info u ON s.fixed_gear_id = u.id
      WHERE s.del_flag = '0' AND u.del_flag = '0'
      AND strftime('%Y', s.schedule_date) = ?
    `;
    const scheduleData = await getAsync(scheduleQuery, [year.toString()]);

    // 步骤3: 活动分析
    const activityQuery = `
      SELECT COUNT(*) as activityCount
      FROM sk_activity
      WHERE del_flag = '0'
      AND strftime('%Y', activity_date) = ?
    `;
    const activityData = await getAsync(activityQuery, [year.toString()]);

    // 构造返回数据
    const result = {
      basicData: {
        hostCount: basicData.hostCount || 0,
        supervisorCount: basicData.supervisorCount || 0,
        partnerCompany: basicData.partnerCompany || 0
      },
      scheduleSummary: {
        host: {
          count: scheduleData.host_count || 0,
          domesticAssignment: scheduleData.host_domestic || 0,
          dispatch: scheduleData.host_dispatch || 0,
          income: scheduleData.host_income || 0
        },
        supervisor: {
          count: scheduleData.supervisor_count || 0,
          domesticAssignment: scheduleData.supervisor_domestic || 0,
          dispatch: scheduleData.supervisor_dispatch || 0,
          income: scheduleData.supervisor_income || 0
        }
      },
      activityAnalysis: activityData.activityCount || 0
    };

    res.json({
      data: result,
      code: '0',
      success: true,
      msg: '统计成功'
    });
  } catch (err) {
    log(`Get dashboard stats error: ${err.message}`);
    res.status(500).json({ data: [],
        code: '1',
        success: false,
        msg: '获取数据失败，请稍后再试！' });
  }
};

// 模块导出
module.exports = { getPersonalScheduleCompletion, getDashboardStats };